<?xml version="1.0" encoding="UTF-8" ?>
<!--

    Copyright Camunda Services GmbH and/or licensed to Camunda Services GmbH
    under one or more contributor license agreements. See the NOTICE file
    distributed with this work for additional information regarding copyright
    ownership. Camunda licenses this file to you under the Apache License,
    Version 2.0; you may not use this file except in compliance with the License.
    You may obtain a copy of the License at

        http://www.apache.org/licenses/LICENSE-2.0

    Unless required by applicable law or agreed to in writing, software
    distributed under the License is distributed on an "AS IS" BASIS,
    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and
    limitations under the License.

-->
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.camunda.bpm.engine.impl.persistence.entity.Commons">

  <!-- SQL snippets that are reused in different mappers -->
  
  <!-- This snippet is used by all queries that allow filtering by variable values -->
  <sql id="variableValueConditions">

    <if test="queryVariableValue.operatorName.equals('NOT_EQUALS') || queryVariableValue.operatorName.equals('NOT_LIKE')">NOT</if><!-- NOT_EQUALS and NOT_LIKE use the same conditions as EQUALS and LIKE -->
    (
    <foreach collection="queryVariableValue.valueConditions" item="valueCondition" separator="or">
      <trim prefix="(" prefixOverrides="and" suffix=")">
        <if test="!valueCondition.type.equals('null')">
          <!-- When type of value is null, type doesn't matter! -->
          and ${varPrefix}${varTypeField} is not null and ${varPrefix}${varTypeField} = #{valueCondition.type}
        </if>
        <if test="valueCondition.textValue != null &amp;&amp; valueCondition.longValue == null &amp;&amp; valueCondition.doubleValue == null">
          <choose>
            <when test="valueCondition.findNulledEmptyStrings">
              and ${varPrefix}TEXT_ is null and ${varPrefix}TEXT2_ = '${@org.camunda.bpm.engine.impl.variable.serializer.StringValueSerializer@EMPTY_STRING}'
            </when>
            <otherwise>
              and ${varPrefix}TEXT_ is not null and
              <choose>
                <when test="queryVariableValue.variableValueIgnoreCase">UPPER(${varPrefix}TEXT_)</when>
                <otherwise>${varPrefix}TEXT_</otherwise>
              </choose>
              <choose>
                <when test="queryVariableValue.operatorName.contains('LIKE')">LIKE</when>
                <otherwise><include refid="org.camunda.bpm.engine.impl.persistence.entity.Commons.executionVariableOperator" /></otherwise>
              </choose>
              <choose>
                <when test="queryVariableValue.variableValueIgnoreCase">UPPER(#{valueCondition.textValue})</when>
                <otherwise>#{valueCondition.textValue}</otherwise>
              </choose>
              ${collationForCaseSensitivity}
              <if test="queryVariableValue.operatorName.contains('LIKE')">ESCAPE ${escapeChar}</if>
            </otherwise>
          </choose>
        </if>
        <if test="valueCondition.textValue2 != null &amp;&amp; !valueCondition.type.equals('string')">
          and ${varPrefix}TEXT2_ is not null and ${varPrefix}TEXT2_
          <choose>
            <when test="queryVariableValue.operatorName.contains('LIKE')">LIKE</when>
            <otherwise><include refid="org.camunda.bpm.engine.impl.persistence.entity.Commons.executionVariableOperator" /></otherwise>
          </choose>
          #{valueCondition.textValue2}
          <if test="queryVariableValue.operatorName.contains('LIKE')">ESCAPE ${escapeChar}</if>
        </if>
    
        <if test="valueCondition.longValue != null">
          and ${varPrefix}LONG_ is not null and ${varPrefix}LONG_
          <include refid="org.camunda.bpm.engine.impl.persistence.entity.Commons.executionVariableOperator" />
          #{valueCondition.longValue}
        </if>
    
        <if test="valueCondition.doubleValue != null">
          and ${varPrefix}DOUBLE_ is not null and ${varPrefix}DOUBLE_
          <include refid="org.camunda.bpm.engine.impl.persistence.entity.Commons.executionVariableOperator" />
          #{valueCondition.doubleValue}
        </if>
  
        <!-- Null variable type -->
        <if test="valueCondition.textValue == null &amp;&amp; valueCondition.textValue2 == null &amp;&amp; valueCondition.longValue == null &amp;&amp; valueCondition.doubleValue == null">
          and ${varPrefix}TEXT_ is null and ${varPrefix}TEXT2_ is null and ${varPrefix}LONG_ is null and ${varPrefix}DOUBLE_ is null and ${varPrefix}BYTEARRAY_ID_ is null
        </if>
      </trim>
    </foreach>
    )
  </sql>

  <sql id="variableNameEqualsCaseInsensitive">
    <choose>
      <when test="queryVariableValue.variableNameIgnoreCase">
        UPPER(${varPrefix}NAME_)= UPPER(#{queryVariableValue.name})
      </when>
      <otherwise>
        ${varPrefix}NAME_= #{queryVariableValue.name}
      </otherwise>
    </choose>
    ${collationForCaseSensitivity}
  </sql>

  <sql id="executionVariableOperator">
    <choose>
      <when test="queryVariableValue.operatorName.equals('EQUALS')">=</when>
      <when test="queryVariableValue.operatorName.equals('NOT_EQUALS')">=</when><!-- Note: NOT_EQUALS is handled by negating the condition -->
      <when test="queryVariableValue.operatorName.equals('GREATER_THAN')">&gt;</when>
      <when test="queryVariableValue.operatorName.equals('GREATER_THAN_OR_EQUAL')">&gt;=</when>
      <when test="queryVariableValue.operatorName.equals('LESS_THAN')">&lt;</when>
      <when test="queryVariableValue.operatorName.equals('LESS_THAN_OR_EQUAL')">&lt;=</when>
   </choose>
  </sql>
  
  <!-- Ordering -->
  
  <!-- Input: property "orderingProperties", a collection of OrderingProperty objects -->
  <!-- Output: property "orderBy", filled with ordering constraints -->
  <!-- Example Output: V0_TEXT_ asc, RES.ID_ desc, RES.NAME_ asc -->
  <sql id="bindOrderBy">
    <choose>
      <when test="orderingProperties.size > 0">
        <foreach collection="orderingProperties" item="orderingProperty" index="index">
          <bind name="newOrderBy" value="@org.camunda.bpm.engine.impl.db.sql.MybatisJoinHelper@orderBy(orderingProperty, index)" />

          <if test="index == 0">
            <bind name="internalOrderBy" value="newOrderBy"/>
          </if>
          <if test="index > 0">
            <bind name="internalOrderBy" value="internalOrderBy + ', ' + newOrderBy"/>
          </if>
        </foreach>
      </when>
      <otherwise>
        <bind name="internalOrderBy" value="'RES.ID_ asc'"/>
      </otherwise>
    </choose>

  </sql>
  
  <!-- Input: property "orderingProperties", a collection of OrderingProperty objects -->
  <!-- Output: a comma-separated list of the ordering columns with aliases; the aliases avoid 
  name clashes with other columns -->
  <!-- Example Output: V0.TEXT_ as V0_TEXT_, V1.DOUBLE_ as V1_DOUBLE_ -->
  <sql id="orderBySelection">
    <foreach collection="orderingProperties" item="orderingProperty" index="index">
      <if test="!orderingProperty.containedProperty">
        <bind name="tableAlias" value="@org.camunda.bpm.engine.impl.db.sql.MybatisJoinHelper@tableAlias(orderingProperty.relation, index)"/>
        ,
        ${@org.camunda.bpm.engine.impl.db.sql.MybatisJoinHelper@orderBySelection(orderingProperty, index)}
        as
        ${tableAlias}_${orderingProperty.queryProperty.name}
      </if>
    </foreach>
  </sql>
  
  <!-- Input: "orderingProperty": an OrderingProperty object; 
              "tableAlias": the table alias for which the filtering conditions apply -->
  <!-- Output: a conjunction of filtering conditions based on the conditions specified in the ordering properties -->
  <!-- Example Output: V0.PROC_INST_ID_ = RES.PROC_INST_ID_ and V0.TEXT_ = 'some value' -->
  <sql id="filterOrderByRelations">
    <if test="orderingProperty.relationConditions != null">
      <foreach collection="orderingProperty.relationConditions" item="relationCondition" separator="and">
        ${tableAlias}.${relationCondition.property.name} 
        = 
        <if test="relationCondition.propertyComparison">
          RES.${relationCondition.comparisonProperty.name} 
        </if>
        <if test="!relationCondition.propertyComparison">
          #{relationCondition.scalarValue}
        </if>
      </foreach>
    </if>
  </sql>

  <!-- Input: "sizeOfIds": size of ID containing list;
              "listOfIds": a list of IDs;
              "fieldName": the field name that should be used before IN clause -->
  <!-- Output: a conjunction of IN clauses -->
  <!-- Example Output: PROC_INST_ID_ IN ( "1", "2" ..... "1000") or IN ("1000", "1001") -->
  <sql id="applyInForPaginatedSql">
	( ${fieldName} IN
	<foreach item="id" index="index" collection="listOfIds" open = "(" separator="" close=")">
	  #{id}
	  <choose>
	  <when test="index != 0 &amp;&amp; (index+1)%1000 == 0 &amp;&amp; (index+1) &lt; sizeOfIds">
		) or ${fieldName} IN (
	  </when>
	  <when test="index == sizeOfIds -1"></when>
	  <otherwise> ,</otherwise>
	  </choose>
	</foreach>
	)
  </sql>

  <sql id="applyInForPaginatedCollection">
    <bind name="sizeOfIds" value="listOfIds.size" />
    <include refid="org.camunda.bpm.engine.impl.persistence.entity.Commons.applyInForPaginatedSql" />
  </sql>

  <sql id="applyInForPaginatedArray">
    <bind name="sizeOfIds" value="listOfIds.length" />
    <include refid="org.camunda.bpm.engine.impl.persistence.entity.Commons.applyInForPaginatedSql" />
  </sql>

  <sql id="periodUnitFunction">
    <choose>
      <when test="reportPeriodUnitName.equals('MONTH')">${constant.datepart.month}</when>
      <when test="reportPeriodUnitName.equals('QUARTER')">${constant.datepart.quarter}</when>
      <when test="reportPeriodUnitName.equals('MINUTE')">${constant.datepart.minute}</when>
    </choose>
  </sql>
</mapper>
